﻿using System;
using System.Data;
using System.Data.SQLite;
namespace StudentManagement.Data
{
    public class DatabaseHelper
    {
        private readonly string _connectionString = "Data Source=student_system.db";
        public DatabaseHelper()
        {
            InitializeDatabase(); // 程序启动时初始化
        }
        public void InitializeDatabase()
        {
            var conn = new SQLiteConnection(_connectionString);
            conn.Open();

            string createSchool = @"
                CREATE TABLE IF NOT EXISTS School (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL
                );";

            string createClass = @"
                CREATE TABLE IF NOT EXISTS Class (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    SchoolId INTEGER NOT NULL,
                    FOREIGN KEY(SchoolId) REFERENCES School(Id)
                );";

            string createStudent = @"
                CREATE TABLE IF NOT EXISTS Student (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    ClassId INTEGER NOT NULL,
                    FOREIGN KEY(ClassId) REFERENCES Class(Id)
                );";

            string createLog = @"
                CREATE TABLE IF NOT EXISTS Log (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Operation TEXT NOT NULL,
                    Timestamp TEXT NOT NULL
                );";

            var cmd = new SQLiteCommand(createSchool + createClass + createStudent + createLog, conn);
            cmd.ExecuteNonQuery();
        }
        public void ExecuteNonQuery(string query, params SQLiteParameter[] parameters)
        {
            var conn = new SQLiteConnection(_connectionString);
            conn.Open();
            var cmd = new SQLiteCommand(query, conn);
            cmd.Parameters.AddRange(parameters);
            cmd.ExecuteNonQuery();
        }

        public DataTable ExecuteQuery(string query, params SQLiteParameter[] parameters)
        {
            var conn = new SQLiteConnection(_connectionString);
            conn.Open();
            var cmd = new SQLiteCommand(query, conn);
            cmd.Parameters.AddRange(parameters);
            var adapter = new SQLiteDataAdapter(cmd);
            var dt = new DataTable();
            adapter.Fill(dt);
            return dt;
        }

        public void LogOperation(string operation)
        {
            string query = "INSERT INTO Log (Operation, Timestamp) VALUES (@op, @time)";
            ExecuteNonQuery(query,
                new SQLiteParameter("@op", operation),
                new SQLiteParameter("@time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
        }
    }
}